﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//The whole script will be re-writed to suit sqlserver

namespace dpmagnet.Data
{
    class DBProc_SQLServer:IDBProcessor
    {

        SqlConnection dbConn;

        public DBProc_SQLServer(String strConn)
        {
            dbConn = new SqlConnection(strConn);
        }


        #region GET DATA

        #region GET TABLES

        public DataTable GetDataTable(string sql)
        {
            DataTable dtResult = new DataTable();
            SqlDataAdapter dbapt = new SqlDataAdapter(sql, dbConn);
            dbConn.Open();
            dbapt.Fill(dtResult);
            dbConn.Close();
            return dtResult;
        }

        #endregion

        #region GET SINGLE DATA

        public decimal GetDecimal(string sql)
        {
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            decimal dResult;
            dbConn.Open();
            dResult=Convert.ToDecimal(dbComm.ExecuteScalar());
            dbConn.Close();
            return dResult;
        }

        public int GetInt(string sql)
        {
            int iResult;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            iResult = Convert.ToInt32(dbComm.ExecuteScalar());
            dbConn.Close();
            return iResult;
        }

        public string GetString(string sql)
        {
            string strResult;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            strResult = dbComm.ExecuteScalar().ToString();
            dbConn.Close();
            return strResult;
        }

        public bool GetBool(string sql)
        {
            bool bResult;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            bResult = Convert.ToBoolean(dbComm.ExecuteScalar());
            dbConn.Close();
            return bResult;
        }

        public object GetObject(string sql)
        {
            object objResult;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            objResult = (object)dbComm.ExecuteScalar();
            dbConn.Close();
            return objResult;
        }

        public DateTime GetDatetime(string sql)
        {
            DateTime dtResult;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            dtResult = Convert.ToDateTime(dbComm.ExecuteScalar());
            dbConn.Close();
            return dtResult;
        }

        #endregion

        #endregion

        #region FUNCTONS

        public bool Insert(string sql)
        {
            bool result;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            if (dbComm.ExecuteNonQuery() <= 0)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            dbConn.Close();
            return result;
        }

        public bool Delete(string sql)
        {
            bool result;
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            if (dbComm.ExecuteNonQuery() <= 0)
            {
                result= false;
            }
            else
            {
                result= true;
            }
            dbConn.Close();
            return result;
        }

        public int Update(string sql)
        {
            SqlCommand dbComm = new SqlCommand(sql, dbConn);
            dbConn.Open();
            int affectedRows = dbComm.ExecuteNonQuery();
            dbConn.Close();
            return affectedRows;
        }

        #endregion

        #region PRIVATE FUNCTIONS



        #endregion
    }
}
